A database is a collection of stored data that is logically related and that is accessible by one or more users. A popular type of database is the relational database management system (RDBMS), which includes relational tables made up of rows and columns. Each row represents an occurrence of an entity defined by a table, with an entity being a person, place, or thing about which the table contains information.
To extract data from, or to update, a relational table, queries according to a standard database query language (e.g., Structured Query Language or SQL) are used. Examples of SQL statements include INSERT, SELECT, UPDATE, and DELETE. There are various ways to submit SQL queries to a database system. One is for a user to sequentially type in SQL statements into a user terminal, with the statements submitted to the database system.
Another technique for accessing and manipulating data in a database system is by use of stored procedures. Stored procedures are implemented according to a stored procedure language (SPL), such as that defined by SQL-99, which defines certain structures for implementing predefined capabilities. Example structures of SPL include conditional execution structures (e.g., IF . . . THEN . . . ELSE, CASE, WHILE, FOR, LOOPING, REPEAT), block structures (e.g., sequences of SQL statements); named variables; and named procedures. Stored procedures are created with a CREATE PROCEDURE statement or a REPLACE PROCEDURE statement. To use a stored procedure, an application program or another stored procedure calls the stored procedure to invoke execution of the stored procedure.
In some conventional database systems, such as one version of the TERADATA® database system from NCR Corporation, some expressions in stored procedures are evaluated by submitting SQL statements. Examples of such expressions include conditional expressions (e.g., WHILE, IF . . . THEN, etc.) and the SET statement. For each occurrence of one of the above expressions, the SQL SELECT statement is submitted in the database system. For example, consider the following SPL source text:
DECLARE V1, V2 INTEGER;
SET V2=V1+10;
The SET statement provided above is converted into the following SQL SELECT statement during stored procedure creation:                USING (spVV0 INTEGER, spVV1 INTEGER) SELECT :spVV0+10 INTO :spVV1;        
The above SQL SELECT statement is submitted each time the SET expression is evaluated during execution of the stored procedure. The same is true for conditional expressions in SPL source text, such as the following WHILE conditional expression:
WHILE (v1+v2<10) DO;
The above WHILE condition is converted into the following SQL SELECT statement:
USING (spVV0 INTEGER, spVV1 INTEGER) SELECT 1                WHERE (:spVV0+:spVV1<10)        
The SQL SELECT statement above is submitted each time the WHILE conditional expression is evaluated during execution of the stored procedure.
The submission of SQL SELECT statements is associated with performance overhead in a database system. Typically, the SELECT statement has to be parsed and actions corresponding to SELECT statements are dispatched to the appropriate entities for performing access or manipulation of data. Also, messages are exchanged during creation of the SQL SELECT statements for SET and conditional expressions in stored procedures.
The increased performance overhead associated with creating and submitting SQL SELECT statements for certain types of expressions in stored procedures reduces the overall available capacity and efficiency of a database system.